BDD1 : modèle relationnel, clé,schéma relationnel

Exemples de situations nécessitant des bases de données

Imaginez les situations développées dans les exemples ci-dessous :

La première approche de ces différents exemples serait une approche ‘tableur’. Vous pouvez relire au passage le chapitre de première sur les tables : accès direct au chapitre table de première.

L’approche tableur pourrait être efficace sur un faible nombre de données mais notre base d’informations deviendrait vite instable ;

• redondance d’informations
• problème de suppression d’informations
• problème de cohérence dans les données : un nom écrit en chiffres, une score écrit avec des lettres, etc.

Quelques éléments d’analyse de l’exemple traitant du QCM

Dans la phase de conception nous pouvons réaliser un tableau qui recense les données issues de notre analyse. Ce tableau correspond à l’analyse du côté matière.

Nous cherchons maintenant à décrire une question (exercice et/ou QCM). Pour écrire une question, il faut :

• Un nombre pour identifier la question de manière unique.
• Le type de question.
• Le niveau.
• La matière.
• Le thème.
• La question
• La réponse (ou les propositions de réponse dans le cadre d’un QCM)
• La solution (ou les solutions) dans le cadre d’un QCM

On traitera de manière particulière les champs niveau, matière et thème car ils existent déjà dans notre analyse. Il va falloir les relier à l’objet. Ce lien sera traité par un mécanisme que l’on appellera clé étrangère. Pour renseigner ces champs il suffira d’aller chercher les identifiants correspondants.

Une première analyse

Entité, attribut, domaine

L’identifiant ou la clé sera appelée clé primaire quand on la considère dans la table qui lui est associée. idMatière est une clé primaire dans la table Matière alors qu’elle devient clé étrangère dans la table Question.

À quel concept (entité ou attribut ou identifiant/clé) pouvez-vous identifier une plaque immatriculation d’un véhicule ?

Une entité est souvent représentée sous la forme :

Dans notre projet, on définit une entité Eleve (On prendra comme convention de ne pas utiliser les accents). Cette entité possède plusieurs attributs :

• IdEleve (domaine : type entier)
• Nom (domaine : chaîne de caractères)
• Prenom (domaine : chaîne de caractères)

L’entité Eleve sera représentée de la manière suivante :

Une fois qu’une entité est définie, les occurrences de cette entité sont appelées instances ou enregistrements ou tuples ou n-uplets.

Eleve

Une autre instance

Eleve

On représente souvent ces tuples dans un tableau.

Les attributs sont les noms se trouvant dans l’en-tête, la première ligne d’un tel tableau.
Les tuples ou enregistrements correspondent aux lignes suivantes d’un tel tableau.

Les associations

Une association définit un lien entre deux entités. Une association possède un nom et éventuellement des attributs qui la caractérisent.

On utilise souvent: * Un verbe pour caractériser une association. * Un nom pour parler d’une entité.

Voici une représentation de nos entités et de notre association :

• Une base de données est un ensemble d’informations, souvent hétérogènes, qui sont structurées de telle sorte que l’insertion, la suppression et l’interrogation d’informations soient possibles.

• Un Système de Gestion de Bases de Données (SGBD) est une interface entre un utilisateur et une base de données.

• Une entité d’une base de données est un objet unique identifié par l’ensemble de ses attributs.

• Un attribut est une information élémentaire liée à une entité portant un nom et une valeur typée.

• Le domaine est l’ensemble des valeurs autorisées pour un attribut donné.

• L’identifiant ou clef est un attribut permettant d’identifié de manière unique une entité.

• Les enregistrements ou tuples sont les instances d’une entité.

• Une association est une liaison entre deux entités. Elle possède un nom et éventuellement des attributs.

Le modèle entité-association

Ce modèle est hors programme dans le cadre de NSI.

Le modèle relationnel

Une vidéo d’introduction au modèle relationnel

Dans le modèle relationnel, les entités et les associations sont transformés en tableaux. Ces tableaux sont appelés relations.

On appelle relation un tableau à deux dimensions dans lequel les attributs correspondent aux colonnes et les $n$-uplets aux lignes.

Dans cet exemple, nous avons accès à une base de données Films qui est composée de plusieurs entités et associations :

• Une entité artiste

• Une entité film

• Une entité internaute

• Une entité pays

• Une association rôle

• Une entité notation

Vision synthétique de la base de données :

Voici quelques exemples de relations.

La relation artiste

La relation rôle

La relation film

Dans la relation artiste ci-dessus :

• Quels sont les attributs ?
• Quel est l’identifiant ?
• Que pouvez-vous dire du type de données des attributs ?
• Citer une occurrence ($n$-uplet).

Dans la relation film ci-dessus :

• Quels sont les attributs ?
• Quel est l’identifiant ?
• Que pouvez-vous dire du type de données des attributs ?
• Citer une occurrence ($n$-uplet).

Dans l’association rôle ci-dessus :

• Quels sont les attributs ?
• Quel est l’identifiant ?
• Que pouvez-vous dire du type de données des attributs ?
• Citer une occurrence ($n$-uplet).

Observer dans les trois tables les correspondances entre les identifiants.

• Retrouver le nom de l’acteur qui joue le rôle de Luke Skywalker.
• Retrouver le nom du réalisateur du film “la guerre des étoiles”.
• Quel rôle joue “Daniels Anthony”

Une clé primaire est un ensemble d’attributs dont les valeurs permettent de distinguer les tuples les uns des autres.
Une clé primaire peut être simple ou composée (de plusieurs attributs).

On utilise souvent un nombre entier (avec le préfixe id) pour prendre le rôle de clé primaire.

Dans la relation Eleve, la clé primaire est l’attribut idEleve, attibut qui est bien de type entier.

Dans l’exemple sur la relation artiste :

• Quel attribut est une clé primaire ?

• Cette clé primaire est-elle simple ou composée ?

• L’ensemble d’attributs (nom, prénom) peut-il être vu comme clé primaire pour cette relation ?

exercice de renforcement

Une clé étrangère est un attribut qui est la clé primaire d’une autre relation. Elle est indiquée par (FK : “foreign key”) ou précédée d’un #.

Reprenons notre exemple avec la base de données Eleve :

On s’intéresse à l’association scolariser. Cette association a des liens vers :

• l’entité eleve par l’intermédiaire de idEle
• l’entité lycee par l’intermédiaire de idLycee
• …

Les attributs idEle, idLycee sont des clés étrangères dans l’association scolariser.

exercice de renforcement

Un schéma relationnel d’une relation est la donnée pour cette relation :

• d’un nom,

• de l’ensemble des attributs en précisant leur domaine propre,

• de (ou des) l’attribut servant de clé primaire et des attributs étant éventuellement des clés étrangères.

Un tel schéma relationnel peut être écrit sous la forme d’un tableau ou sous forme textuelle.

On peut transformer une entité en relation donnée par son schéma relationnel.

• Les attributs de l’entité deviennent les attributs du schéma relationnel (de même nom que l’entité).

• L’identifiant devient clé primaire.

• Dans un schéma relationnel la clé primaire est mise en évidence soit avec l’indication CP (ou PK pour Primary Key), soit en étant soulignée, soit avec le dessin d’une clé, soit indiquée à l’aide d’une remarque.

On peut transformer en relation donnée par son schéma relationnel, l’entitéEleve.

devient :

On peut également retrouver une notation textuelle où la clé primaire est soulignée : Eleve(IdEleve, Nom, Prenom)

Dans un schéma relationnel, une clé étrangère est mise en évidence soit avec l’indication FK (pour Foreign Key), soit en étant précédée d’un #.

devient

En notation textuelle où les clés étrangères sont précédées d’un symbole # : scolariser(#idLycee, #idEleve, #idNiveau, nomClasse)

Une base de données relationnelle est un ensemble de relations.
L’ensemble des schémas relationnels appelé schéma de la base de données.

exercice de renforcement

Contrainte d’entité

L’existence et l’unicité des clés primaires : une clé primaire ne peut être vide et il ne peut y avoir de doublons dans une relation. Toute relation doit posséder une clé unique que l’on appelle clé primaire. Cela définit la contrainte d’entité (appelée aussi contrainte de relation).

Le problème typique est l’utilisation de l’attribut nomEle dans notre entité Eleve. Cet attribut ne peut définir de manière unique un élève car plusieurs élèves peuvent avoir le même nom. Nous ne pouvons donc pas utiliser cet attribut comme clé primaire. Dans notre exemple nous avons utilisé un attribut idEle. Chaque élève est donc identifié par un numéro unique (unicité); de plus, chaque a nécessairement un tel numéro d’identification (existence).

Contrainte de domaine

Les données que nous souhaitons stocker dans notre base de données ont des formats différents. On parle alors de domaine. On peut s’inspirer des types de données des langages de programmation que nous avons étudiés (integer, booléens, float, char, string).

Les contraintes de domaines doivent permettre de :

• représenter les données sans perte d’information,

• d’éviter les erreurs de saisies.

Tout attribut d’un n-uplet doit prendre une valeur appartenant à l’ensemble des valeurs possibles prédéfinies pour cet attribut : on parle de contrainte de domaine.

La relation Eleve(IdEleve, Nom, Prenom) a trois attributs :

• L’attribut IdEleve est nécessairement un entier,

• Les attributs Nom et Prenom sont nécessairement des chaînes de caractères.

Ainsi, le n-uplet (3, "Beau", "Gosse") peut être un tuple de la relation Eleve.
Par contre, ("trois", "Go", "Boss") ne peut pas être un tuple de la relation Eleve car “trois” n’est pas un nombre entier.

Contrainte de référence

Nous utilisons les clés primaires afin de distinguer de manière unique nos entités. Ces clés primaires servent également de références dans les autres relations. Il faut veiller à ce que les références soient effectives. On ne peut pas définir une entité qui fait référence par une clé étrangère à une entité qui n’existe pas.

La valeur d’un attribut qui est une clé étrangère doit nécessairement faire référence à un n-uplet existant dans la relation dont cette clé étrangère est la clé primaire : on parle de contrainte de référence.

Reprenons notre relation scolariser : scolariser(#idLycee, #idEleve, #idNiveau, nomClasse)

Dans cette relation : idLycee, idEleve, idNiveau sont des clés étrangères. On ne peut scolariser que des élèves connus dans des lycées connus sur des niveaux connus. Pour ajouter une nouvelle scolarisation d’un élève fictif, il faudra que l’élève soit existant, que son lycée soit existant et que sa classe soit connue. Il sera également impossible de supprimer les entités Lycee ou Eleve

exercice de renforcement

• Une relation est un tableau à deux dimmensions dans lequel les attributs sont les colonnes et les tuples ou enregistrements sont les lignes.

• Une clef primaire est un ensemble d’attributs non vide qui permet d’identifier de manière unique une relation.

• Une clef étrangère d’une relation est un attribut étant aussi la clé primaire d’une autre relation.

• Un schéma relationnel d’une relation est un ensemble formé du nom de la relation, de ses attributs en précisant le domaine et les clef primaire et éventuellemnt étrangères.

• Une clé primaire est reconnaissable soit par un soulignement, soit par une dessin de clé, soit par les lettres CP ou PK.

• Une clé primaire peut être simple (définie par un seul attribut) ou composée (définie par un ensemble de plusieurs attributs)

• Une clé étrangère est reconnaissable soit par le symbole #, soit par les lettres FK.

• La contrainte d’entité ou contrainte de relation est le fait que toute relation doit avoir une clé primaire unique.

• La contrainte de domaine est le fait que tout atttribut d’enregistrement doit prendre ses valeurs dans un ensemble de valeurs possibles précis.

• La contrainte de référence est le fait que toute valeur de clé étrangère doit faire référence à un enregistrement existant dans la relation dont cette clef étrangère est la clef primaire.

clefs primaire et étrangères

Une restauratrice a mis en place un site Web pour gérer ses réservations en ligne. Chaque client peut s’inscrire en saisissant ses identifiants. Une fois connecté, il peut effectuer une réservation en renseignant le jour et l’heure. Il peut également commander son menu en ligne et écrire un avis sur le restaurant.
Le gestionnaire du site Web a créé une base de données associée au site nommée restaurant, contenant les quatre relations du schéma relationnel ci-dessous :

1. 1. Quelle est la clef primaire de la relation Plat ?

   2. Cette clé primaire est-elle simple ou composée ?

   3. Quel est le domaine de cette clef ?

2. 1. Quelle est la clef primaire de la relation Commande ?

   2. Cette clé primaire est-elle simple ou composée ?

3. 1. L’attribut idClient peut-il être la clé primaire de la relation Reservation ? Pourquoi ?

   2. Le couple passwd, nom peut-il être la clé primaire de la relation Client ? Pourquoi ?

Cliquer pour afficher la solution

1. 1. La clef primaire de la relation Plat est l’attribut souligné idPlat.

   2. Cette clé primaire est simple car elle est constituée d’un seul attribut.

   3. Le domaine de l’attribut idPlat est le type entier.

2. 1. La clef primaire de la relation Commande est le couple d’attributs soulignés idPlat, idReservation

   2. Cette clé primaire est composée car elle est constituée de plusieurs attributs.

3. 1. L’attribut idClient ne peut pas être la clé primaire de la relation Reservation car cet attribut n’est pas différent pour chaque réservation : un même client peut effectuer plusieurs réservations.

   2. Le couple passwd, nom ne peut pas être la clé primaire de la relation Client car il ne permet pas d’identifier de manière unique un client. Deux clients différents peuvent porter le même nom et avoir choisi par hasard le même mot de passe, quand bien même la probabilité de cela soit très faible.

Une restauratrice a mis en place un site Web pour gérer ses réservations en ligne. Chaque client peut s’inscrire en saisissant ses identifiants. Une fois connecté, il peut effectuer une réservation en renseignant le jour et l’heure. Il peut également commander son menu en ligne et écrire un avis sur le restaurant.
Le gestionnaire du site Web a créé une base de données associée au site nommée restaurant, contenant les quatre relations du schéma relationnel ci-dessous :

1. Quelles relations admettent des clefs étrangères ?

2. Quelle est la clef étrangère de la relation Reservation ?

3. Vers quelle relation cette étrangère établit-elle une liaison ?

4. Quel est le domaine de cette clef ?

5. Combien de clefs étrangères apparaissent dans la relation Commande ?

Cliquer pour afficher la solution

1. Les relations Reservation et Commande admettent des clefs étrangères, ce sont les attributs matérialisés par le symbole #.

2. La clef étrangère de la relation Reservation est l’attribut idClient.

3. La clef étrangère idClient établit une liaison vers la relation Client.

4. Le domaine de la clef étrangère idClient est une chaîne de caractères (d’après le type VARCHAR).

5. Deux clefs étrangères apparaissent dans la relation Commande : idPlat et idReservation.

Schéma relationnel

Vous créez un jeu vidéo en ligne. Vous voulez gérer les comptes des joueurs avec une base de données.
Cette base de données contient plusieurs entités :

• Une entité Joueur qui contient le nom, le mot de passe, le statut du joueur et le héros joué en ligne.

• Une entité statut qui permet de savoir si le joueur a un compte premium ou nom grâce à un attribut de type booléen.

• Une entité Heros qui permet de savoir le type de héros choisi, son niveau d’expérience, son nom choisi par le joueur.

Établir le schéma relationnel de cette base de données en précisant le type (INT, STRING ou BOOLEAN) de chaque attribut.

Cliquer pour afficher la solution

Voici le schéma relationnel de la base de données :

• L’entité Joueur est modélisée par la relation Joueur(idJoueur:INT,nomJoueur:STRING ,password:STRING,#idStatut:INT,#idHeros:INT).

• L’entité Statut est modélisée par la relation Statut(idStatut:INT,premium:BOOLEAN).

• L’entité Heros est modélisée par la relation Heros(idHeros:INT,typeHeros:STRING, nivExpHeros:INT,nomHeros:STRING).

Contraintes d’intégrité

Vous avez créé un jeu en ligne et vous gérez le compte des joueurs inscrits par l’intermédiaire d’une base de données.
Suite à une tentative d’attaque informatique, votre base de données a peut-être été corrompue.

Pour le savoir vous étudiez des extraits des cinq relations définissant votre base de données :

• Joueur(IdJoueur:INT,nomJoueur:STRING,pseudoJoueur:STRING,ageJoueur:INT,#idPersonnage:INT),

• Personnage(IdPersonnage:INT,nomPersonnage:STRING,typePersonnage:STRING,experiencePersonnage:INT,#idArme:INT,#idVisuel:INT),

• Arme(IdArme:INT,nomArme:STRING),

• Visuel(IdVisuel:INT,#idGenre:INT,typeVisuel:STRING).

• Genre(IdGenre:INT,nomGenre:STRING).

Voici :

• un extrait de la relation Joueur qui représente les cinq premières lignes de la table ordonnée par ordre croissant des valeurs de l’attribut idJoueur :

• un extrait de la relation Personnage qui représente les cinq premières lignes de la table ordonnée par ordre croissant des valeurs de l’attribut idPersonnage :

• un extrait de la relation Arme qui représente les cinq premières lignes de la table ordonnée par ordre croissant des valeurs de l’attribut idArme :

• un extrait de la relation Visuel qui représente les cinq premières lignes de la table ordonnée par ordre croissant des valeurs de l’attribut idVisuel :

• la relation Genre :

Repérez-vous des anomalies dans ces tables ? Si oui, quelles contraintes ne sont pas respectées ?

Cliquer pour afficher la solution

• À la deuxième ligne de la table Joueur manque la référence idPersonnage : la contrainte de référence n’est pas respectée.

• À la troisième ligne de la table Joueur l’âge n’est pas de type entier : la contrainte de domaine n’est pas respectée.

• À la quatrième ligne de la table Joueur la référence 4 de la clé étrangère idPersonnage correspond à aucune valeur de la clé primaire idPersonnage de la table Personnage (puique les n-uplets sont rangés dans l’ordre croissant des valeurs de idPersonnage) : la contrainte de reférence n’est pas respectée.

• Les quatrième et cinquième ligne de la table Personnage sont identifiées par la même valeur de la clé primaire idPersonnage : la contrainte d’entité (ou de relation) n’est pas respectée.